import time
import traceback
import pymysql

class pymysqlHandle(object):
    ''' 定义一个 MySQL 操作类'''


    def __init__(self,host,username,password,port,database=None):
        '''初始化数据库信息并创建数据库连接'''
        # 下面的赋值其实可以省略，connect 时 直接使用形参即可
        self.host = host
        self.username = username
        self.password = password
        self.database = database
        self.port = port

        if database == None or database == '':
            self.db = pymysql.connect(host=self.host, user=self.username, password=self.password, port=self.port, charset='utf8mb4')
            self.cursor = self.db.cursor()
        else:
            self.db = pymysql.connect(host=self.host, user=self.username, password=self.password, port=self.port, charset='utf8mb4')

    def creat_database(self, db_name):
        ''' 数据库创建 '''
        # 创建数据库的sql(如果数据库存在就不创建，防止异常)
        rt = self.cursor.execute("CREATE DATABASE IF NOT EXISTS %s" % db_name)
        return rt

    def create_table(self,table_sql):
        ''' 数据表创建 '''
        try:
        # 执行sql
            self.cursor.execute(table_sql)
            # self.cursor.commit()
            return True
        except :
            self.db.rollback()
            traceback.print_exc()
            return None

    # def connect_database(self):
    #     ''' 数据库连接 '''
    #     self.db = pymysql.connect(self.host,self.username,self.password,self.database,self.port,charset='utf8')
    #     # self.cursor = self.db.cursor()
    #     return self.db

    def insertDB(self,sql):
        ''' 插入数据库操作 '''
        self.cursor = self.db.cursor()
        try:
            # 执行sql
            rt = self.cursor.execute(sql)
            self.db.commit()
            return rt
        except :
            # 发生错误时回滚
            self.db.rollback()
            traceback.print_exc()
            return None
        finally:
            self.cursor.close()

    def deleteDB(self,sql):
        ''' 操作数据库数据删除 '''
        self.cursor = self.db.cursor()
        try:
            # 执行sql
            rt = self.cursor.execute(sql)
            self.db.commit()
            return rt
        except:
            # 发生错误时回滚
            self.db.rollback()
            traceback.print_exc()
            return None
        finally:
            self.cursor.close()

    def updateDb(self,sql):
        ''' 更新数据库操作 '''
        self.cursor = self.db.cursor()
        try:
            # 执行sql
            rt = self.cursor.execute(sql)
            self.db.commit()
            return rt
        except:
            # 发生错误时回滚
            self.db.rollback()
            traceback.print_exc()
            return None
        finally:
            self.cursor.close()

    def selectDb(self,sql):
        ''' 数据库查询 '''
        self.cursor = self.db.cursor(pymysql.cursors.DictCursor)
        try:
            rt = self.cursor.execute(sql) # 返回 查询数据 条数 可以根据 返回值 判定处理结果
            data = self.cursor.fetchall() # 返回所有记录列表
            return data
            # 结果遍历
            # for row in data:
            #     sid = row[0]
            #     name = row[1]
                # 遍历打印结果
                # print('sid = %s,  name = %s'%(sid,name))
        except:
            # print('Error: unable to fecth data')
            traceback.print_exc()
            return None
        finally:
            self.cursor.close()



    def closeDb(self):
        ''' 数据库连接关闭 '''
        self.cursor.close()
        self.db.close()



if __name__ == '__main__':
    # DbHandle = pymysqlHandle('127.0.0.1','adil','helloyyj','AdilTest',3306)
    # DbHandle.insertDB('insert into test(name) values ("%s")'%('FuHongXue'))
    # DbHandle.insertDB('insert into test(name) values ("%s")'%('FuHongXue'))
    # DbHandle.selectDb('select * from test')
    # DbHandle.updateDb('update test set name = "%s" where sid = "%d"' %('YeKai',22))
    # DbHandle.selectDb('select * from test')
    # DbHandle.insertDB('insert into test(name) values ("%s")'%('LiXunHuan'))
    # DbHandle.deleteDB('delete from test where sid > "%d"' %(25))
    # DbHandle.selectDb('select * from test')
    # DbHandle.closeDb()
    pydb = pymysqlHandle('localhost', 'root', 'root', 3306, 'djangoserver')
    sheet= '凤凰'
    # pydb.selectDb("SELECT project_name FROM projectlist WHERE project_name='%s'"%sheet)
    # pydb.insertDB("INSERT INTO projectlist SET project_name='%s',project_type='WebUI',create_time='2021-03-23 01:58:04';" % (sheet))
    # create_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    # pydb.insertDB("INSERT INTO projectlist SET project_name='%s',project_type='WebUI',create_time='%s';" % (sheet, create_time))
    proid = pydb.selectDb("SELECT id FROM projectlist WHERE project_name='%s'" % sheet)
    print(proid[0]['id'])

